package model;

import helperclasses.DbUtil;
import helperclasses.MyDataColector;
import helperclasses.Product;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.Iterator;

import javax.sql.DataSource;

public class ModifyProduct {
	MyDataColector mydataColector;

	public ModifyProduct() {
		mydataColector = new MyDataColector();
	}

	private Connection getConnection() throws SQLException {

		DataSource ds = DbUtil.getInstance().getDataSource();
		Connection conn = ds.getConnection();
		return conn;
	}

	public boolean EditProduct(Product editProd) throws SQLException, ParseException {
		Connection conn = getConnection();
		Statement st = conn.createStatement();
		String sql1, sql2;
		boolean succes = true;
		ResultSet result;
		double cantitateVeche = 0, valoareInStocVeche = 0, diferentaCantitati = 0;
		java.text.DecimalFormat df = new DecimalFormat("########,####");

		try {
			conn.setAutoCommit(false);
			sql1 = "SELECT cantitate, valoare_in_stoc FROM produse_in_stoc WHERE produs_id=" + editProd.getProductId();
			result = st.executeQuery(sql1);
			while (result.next()) {
				cantitateVeche = Double.parseDouble(result.getString("cantitate"));
				valoareInStocVeche = Double.parseDouble(result.getString("valoare_in_stoc"));
			}
			if (cantitateVeche != editProd.getCantitate()) {
				diferentaCantitati = valoareInStocVeche - (cantitateVeche * editProd.getValoare());
				diferentaCantitati = (double) Math.round(diferentaCantitati * 10000) / 10000;
				editProd.setValoareInStoc((editProd.getCantitate() * editProd.getValoare()) + diferentaCantitati);
			}

			// Update table Produse In Stoc
			sql1 = "UPDATE produse_in_stoc SET cantitate=" + editProd.getCantitate() + ", unitate_masura_id="
					+ mydataColector.getUnitateMasuraId(editProd.getUnitateMasura()) + ", valoare_in_stoc="
					+ editProd.getValoareInStoc() + " WHERE produs_id=" + editProd.getProductId();
			st.executeUpdate(sql1);
			// Update table Produse
			sql2 = "UPDATE produse SET nume=\"" + editProd.getProductName() + "\", valoare=" + editProd.getValoare()
					+ ", tip_nir=" + editProd.getTipNir() + ", pret_iesire=" + editProd.getPretIesire() + " WHERE produs_id="
					+ editProd.getProductId();
			st.executeUpdate(sql2);
			if (editProd.getTipNir() == 3) {
				// We delete all entries in the retetar table
				sql1 = "DELETE FROM retetar WHERE cocktail_id=" + editProd.getProductId();
				st.executeUpdate(sql1);
				// Now we add the new values
				// we have to get product id of ingredients and insert the data
				// in the retetar table
				Iterator iterator = editProd.getIngredients().keySet().iterator();
				while (iterator.hasNext()) {
					Product ingredient = new Product();
					ingredient = editProd.getIngredients().get((Integer) iterator.next());
					sql2 = "INSERT INTO retetar(cocktail_id, produs_id_retetar, valoare) VALUES(" + editProd.getProductId()
							+ ", " + ingredient.getProductId() + ", " + ingredient.getValoareInStoc() + ")";
					st.executeUpdate(sql2);
				}
			}
			conn.commit();

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				// An error occured so we rollback the changes.
				conn.rollback();
				succes = false;
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		} finally {
			st.close();
			conn.close();
		}
		return succes;
	}

	public boolean SaveProduct(Product saveProd) throws SQLException {

		if (CheckForDuplicate(saveProd.getProductName()))
			return false;

		Connection conn = getConnection();
		Statement st = conn.createStatement();
		String sql1 = "", sql2 = "";
		int product_id = 0;
		int unitate_masura_id = 0;
		ResultSet result;
		boolean succes = true;

		try {
			conn.setAutoCommit(false);
			// Insert data into produse
			sql1 = "INSERT INTO produse (produs_id, nume, valoare, pret_iesire, tip_nir) VALUES(null, '"
					+ saveProd.getProductName() + "', " + saveProd.getValoare() + ", " + saveProd.getPretIesire() + ", "
					+ saveProd.getTipNir() + ")";

			// code used to get the last generated ID
			st.executeUpdate(sql1, Statement.RETURN_GENERATED_KEYS);
			result = st.getGeneratedKeys();
			if (result.next()) {
				product_id = result.getInt(1);
			}
			if (saveProd.getTipNir() == 3) {
				// we have to get product id of ingredients and insert the data
				// in the retetar table
				Iterator iterator = saveProd.getIngredients().keySet().iterator();
				while (iterator.hasNext()) {
					Product ingredient = new Product();
					ingredient = saveProd.getIngredients().get((Integer) iterator.next());
					sql2 = "INSERT INTO retetar(cocktail_id, produs_id_retetar, valoare) VALUES(" + product_id + ", "
							+ ingredient.getProductId() + ", " + ingredient.getValoareInStoc() + ")";

					st.executeUpdate(sql2);
				}
			}
			unitate_masura_id = mydataColector.getUnitateMasuraId(saveProd.getUnitateMasura());
			// Insert data into produse_in_stoc
			sql2 = "INSERT INTO produse_in_stoc (produs_id, cantitate, valoare_in_stoc, unitate_masura_id) VALUES(" + product_id
					+ ", " + saveProd.getCantitate() + ", " + saveProd.getValoareInStoc() + ", " + unitate_masura_id + ")";

			st.executeUpdate(sql2);
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				// An error occured so we rollback the changes.
				conn.rollback();
				succes = false;
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		} finally {
			st.close();
			conn.close();
		}
		return succes;
	}

	private boolean CheckForDuplicate(String productName) throws SQLException {
		String sql;
		ResultSet result;
		Connection conn = getConnection();
		Statement st = conn.createStatement();

		sql = "SELECT * FROM produse WHERE nume ='" + productName + "'";
		result = st.executeQuery(sql);
		while (result.next()) {
			st.close();
			conn.close();
			return true;
		}
		st.close();
		conn.close();
		return false;
	}
}
